#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3
import csv
import SimpleHTTPServer
import SocketServer
conexao = sqlite3.connect("myDb.db")
c = conexao.cursor()


spamReader = csv.reader(open('rebcsv/reb0.csv','rb'),delimiter=',')

c.execute('''drop table if exists professores''')
conexao.commit()

c.execute('''create table if not exists professores(cod integer, nome text, grau text, curso text, categoria text, regime text, anoLectivo integer, tipoEstabelicimento text, nomeEstabelicimento text)''')



#create the http server
PORT = 8000
Handler = SimpleHTTPServer.SimpleHTTPRequestHandler

httpServerRunning = False

while not httpServerRunning == True:
    try:
        httpd = SocketServer.TCPServer(("", PORT), Handler)
        httpServerRunning = True
    except:
        httpServerRunning = False
        PORT = PORT + 1
        pass
    pass


#creates the html file and loads to it the list of students
ficheiro = file("index.html","w")
ficheiro.write("<html><body>")
ficheiro.write("<ul>")
i = 0
for row in spamReader:
    i = i+1
    if i != 1:
        try:
            stmt = '''Insert into professores(cod,nome,grau,curso,categoria,regime,anoLectivo,tipoEstabelicimento,nomeEstabelicimento) values ("{0}","{1}","{2}","{3}","{4}","{5}","{6}","{7}","{8}")'''.\
                format(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8])
            c.execute(stmt)
        except:
            pass
        pass
    pass

stmt = '''Select * from professores'''
c.execute(stmt)

for row in c:
    toListOnWebPage =str(row[0]),row[1],row[2],row[3],row[4],row[5],str(row[6]),row[7],row[8]
    ficheiro.write("<li>" + str(toListOnWebPage))
    pass
ficheiro.write("</ul>")
ficheiro.write("</body></html>")

conexao.commit()
ficheiro.close()
conexao.close()
print "Server running at port " + str(PORT)
httpd.serve_forever()
